<?php
class Route {

    public function __construct() {
        $this->SQL = MySQL::getInstance();
    }

    /*
     * get - get all information on a single route
     * @param id int route id
     * @return array of form array(location_id, difficulty_id, colour_id, is_short, author_id, removed, name, created, location, difficulty, colour, author)
     */
    public function get($id) {
        return $this->SQL->fetch_single('
            SELECT r.id, r.location_id, r.difficulty_id, r.colour_id, r.is_short, r.author_id, r.removed, r.name,
                NULLIF(r.created, "0000-00-00") AS created, r.type_id, r.rope, rt.name AS type_name, rt.unicode, rt.type AS route_type,
                l.name AS location, d.name AS difficulty, c.name AS colour, a.name AS author
            FROM routes r
            JOIN locations l
                ON l.id = r.location_id
            JOIN difficulties d
                ON d.id = r.difficulty_id
            JOIN colours c
                ON c.id = r.colour_id
            JOIN route_types rt
                ON rt.id = r.type_id
            LEFT JOIN authors a
                ON a.id = r.author_id
            WHERE r.id = %d
            LIMIT 1',
            $id
        );
    }

    /*
     * getAll - get all routes at a given location
     * @param locationId int location id
     * @return array of form array(id, is_short, created, difficulty, colour)
     */
    public function getAll($locationId) {
        if(!$locationId) {
            return FALSE;
        }
        return $this->SQL->fquery('
            SELECT r.id, r.is_short, r.name, NULLIF(r.created, "0000-00-00") AS created, d.name AS difficulty, c.name AS colour,
                rt.name AS type_name, rt.type AS route_type, rt.unicode, r.rope
            FROM routes r
            JOIN difficulties d
                ON d.id = r.difficulty_id
            JOIN colours c
                ON c.id = r.colour_id
            JOIN route_types rt
                ON rt.id = r.type_id
            WHERE r.location_id = %d
            AND r.removed = 0
            ORDER BY r.is_short DESC, d.value, c.name',
            $locationId
        );
    }

    /*
     * add - add a route to the database
     * @param locationId int location id
     * @param data array of form array(difficulty_id, colour_id, type_id, is_short, [author_id, date])
     * @param userId int current user id
     * @return bool TRUE/FALSE
     */
    public function add($locationId, $data, $userId) {
        if(!$locationId || !isset($data['difficulty_id']) || !isset($data['colour_id']) || !$data['difficulty_id'] || !$data['colour_id']) {
            return FALSE;
        }
        if(!$userId) {
            return FALSE;
        }
        if(!isset($data['is_short'])) {
            $data['is_short'] = 0;
        }
        return $this->SQL->fquery('
            INSERT INTO routes
            (name, location_id, difficulty_id, colour_id, type_id, rope, is_short, author_id, created, added_by, removed)
            VALUES
            ("%s", %d, %d, %d, %d, "%s", %d, %d, "%s", %d, 0)',
            addslashes($data['name']),
            $locationId,
            $data['difficulty_id'],
            $data['colour_id'],
            $data['type_id'],
            $data['rope'],
            $data['is_short'],
            ($data['author_id'] ? $data['author_id'] : NULL),
            ($data['date'] ? date('Y-m-d', strtotime($data['date'])) : NULL),
            $userId
        );
    }

    /*
     * update - update the information on a route
     * @param id int route id
     * @param data array of form array(difficulty_id, colour_id, type_id, is_short, author_id, created, removed)
     * @return bool TRUE/FALSE
     */
    public function update($id, $data) {
        if(!$id || !isset($data['difficulty_id']) || !isset($data['colour_id']) || !$data['difficulty_id'] || !$data['colour_id']) {
            return FALSE;
        }
        if(!isset($data['is_short'])) {
            $data['is_short'] = 0;
        }
        if(!isset($data['removed'])) {
            $data['removed'] = 0;
        }
        return $this->SQL->fquery('
            UPDATE routes
            SET difficulty_id = %d,
                colour_id = %d,
                type_id = %d,
                rope = "%s",
                is_short = %d,
                author_id = %d,
                name = "%s",
                created = "%s",
                removed = %d
            WHERE id = %d
            LIMIT 1',
            $data['difficulty_id'],
            $data['colour_id'],
            $data['type_id'],
            addslashes($data['rope']),
            ($data['is_short'] ? 1 : 0),
            ($data['author_id'] ? addslashes($data['author_id']) : 'NULL'),
            addslashes($data['name']),
            ($data['created'] ? date('Y-m-d', strtotime($data['created'])) : 'NULL'),
            ($data['removed'] ? 1 : 0),
            $id
        );
    }

    /*
     * getStats - get statistics on a route
     * @param id int route id
     * @return array data of form array(count, usercount, completed)
     */
    public function getStats($id) {
        $ret = $this->SQL->fetch_single('
            SELECT COUNT(*) AS `count`, COUNT(DISTINCT(user_id)) AS usercount
            FROM users_routes_link
            WHERE route_id = %d
            LIMIT 1',
            $id
        );
        $ret['completed'] = $this->SQL->fetch_value('
            SELECT COUNT(*)
            FROM users_routes_link
            WHERE route_id = %d
            AND completed >= 0
            LIMIT 1',
            $id
        );
        return $ret;
    }

    /*
     * getLatestComments - get the x newest comments made on a route with checkins
     * @param id int route id
     * @param count int optional number of comments to return
     * @return array data of form array(user_id, name, timestamp, comment, completed)
     */
    public function getLatestComments($id, $count = 10) {
        return $this->SQL->fquery('
            SELECT url.user_id, u.name, url.timestamp, url.comment, url.completed
            FROM users_routes_link url
            JOIN users u
                ON u.id = url.user_id
            WHERE url.route_id = %d
            AND url.comment IS NOT NULL
            AND url.comment != ""
            ORDER BY url.timestamp DESC
            LIMIT %d',
            $id,
            $count
        );
    }

    /*
     * getRouteLocationId - get the location id for a given route
     * @param routeId int route id
     * @return int location id
     */
    public function getRouteLocationId($routeId) {
        return $this->SQL->fetch_value('
            SELECT location_id
            FROM routes
            WHERE id = %d
            LIMIT 1',
            $routeId
        );
    }

    /*
     * getTypes - get all types of routes (slab, overhang, vertical and combinations thereof)
     * @return array of form (id, name, unicode)
     */
    public function getTypes() {
       return $this->SQL->fquery('
          SELECT id, name, unicode, type
          FROM route_types
          ORDER BY name'
       );
    }
}
?>
